﻿using Common.AspNetCore;
using Common.Infrastructure;
using File.Api.Models;
using File.Api.Service;
using File.Api.Utils;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using static Common.Enums.File.FileEnum;

namespace File.Api.Controllers
{
    [Route("api/IQCExcel")]
    public class IQCExcelController : ApiController
    {
        private readonly PdfService pdfService;

        public IQCExcelController(IServiceProvider provider, PdfService pdfService)
           : base(provider)
        {
            this.pdfService = pdfService;
        }

        /// <summary>
        /// IQC导出
        /// </summary>
        /// <param name="orderModel"></param>
        /// <returns></returns>
        [AllowAnonymous]
        [HttpPost, Route("ExportIQCOrderExcel")]
        public ActionResult ExportOQCOrderExcel([FromBody] IQCOrderModel orderModel)
        {
            if (!orderModel.DetailModels.Any())
            {
                return null;
            }

            if (!orderModel.VersionModels.Any())
            {
                return null;
            }

            if (!orderModel.VersionModels.OrderByDescending(p => p.Versions).FirstOrDefault().QCOrderItemModels.Any())
            {
                return null;
            }

            var version = orderModel.VersionModels.OrderByDescending(p => p.Versions).FirstOrDefault();
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet();
            GenerateSheetSetColunmsWidth(sheet);
            int rowNum = 0;
            GenerateExcelIQCHeader(workbook, sheet, rowNum, orderModel.KABU == 0 ? orderModel.VersionModels.FirstOrDefault().SamplingSchemeName : string.Empty);
            rowNum += 1;
            GenerateSheetIQCHeader(workbook, sheet, rowNum, orderModel);

            rowNum += 5;
            if (orderModel.KABU == 1)
            {
                rowNum++;
            }

            version.QCOrderItemModels = version.QCOrderItemModels.Where(p => p.Result != 0).ToList();
            var groupItem = version.QCOrderItemModels.OrderBy(p => p.CategoryType).GroupBy(p => p.CategoryCode);
            groupItem.ForEach(o =>
            {
                var items = version.QCOrderItemModels.Where(p => p.CategoryCode == o.Key).OrderBy(p => p.Index).ToList();
                var defects = version.SamplingSchemeDefects.Where(p => p.ItemCategoryCode == o.Key).ToList();
                if (o.FirstOrDefault().CategoryType == 0)
                {
                    AddBatchItemIQCQualitativeRow(workbook, sheet, ref rowNum, items, defects, o.FirstOrDefault().CategoryName);
                    rowNum++;
                }
                else if (o.FirstOrDefault().CategoryType == 1)
                {
                    AddBatchItemDataIQCRationRow(workbook, sheet, ref rowNum, items, defects, o.FirstOrDefault().CategoryName);
                    rowNum++;
                }
            });

            GenerateSheetIQCFooter(workbook, sheet, rowNum, orderModel);
            if (orderModel.Type == 0)
            {
                using (var ms = new MemoryStream())
                {
                    workbook.Write(ms, false);
                    var name = $"{version.SamplingSchemeName}_{DateTime.Now}.xlsx";
                    return File(ms.ToArray(), "application/octet-stream", name);
                }
            }

            using (var ms = new MemoryStream())
            {
                workbook.Write(ms, false);
                var name = $"{version.SamplingSchemeName}_{DateTime.Now}.pdf";
                return File(pdfService.Render(ms.ToArray()), "application/octet-stream", name);
            }
        }

        private void AddBatchItemIQCQualitativeRow(IWorkbook workbook, ISheet sheet, ref int rowNum, List<QCOrderItemModel> items, List<QCOrderCategoryDefectModel> defects, string categoryName)
        {
            var rowCount = AddBatchItemDataIQCHeaderRow(workbook, sheet, ref rowNum, items, defects, categoryName);
            ICellStyle boldStyle = ExcelStyleUtils.GetExcelOQCStyle(workbook);
            IRow headerRow = sheet.CreateRow(rowNum);
            ///headerRow = sheet.CreateRow(rowNum);
            if (rowCount == 0)
            {
                headerRow.CreateCell(0, CellType.String).SetCellValue(categoryName);
            }

            headerRow.CreateCell(1, CellType.String).SetCellValue("NO");
            headerRow.CreateCell(2, CellType.String).SetCellValue("检验项目");
            headerRow.CreateCell(7, CellType.String).SetCellValue("AC");
            headerRow.CreateCell(9, CellType.String).SetCellValue("RE");
            headerRow.CreateCell(11, CellType.String).SetCellValue("抽样数");
            headerRow.CreateCell(15, CellType.String).SetCellValue("不良数");
            headerRow.CreateCell(18, CellType.String).SetCellValue("异常描述");
            headerRow.CreateCell(30, CellType.String).SetCellValue("缺陷等级");
            headerRow.CreateCell(34, CellType.String).SetCellValue("判定");
            headerRow.CreateCell(37, CellType.String).SetCellValue("备注");
            IQCQualitativeMergedRegion(workbook, sheet, rowNum);
            ExcelStyleUtils.SetRowWrapText(workbook, headerRow);

            for (var i = 1; i <= items.Count; i++)
            {
                rowNum++;
                rowCount++;
                var item = items[i - 1];
                headerRow = sheet.CreateRow(rowNum);
                var badRemark = string.Empty;
                item.QCOrderItemBadModels.ForEach(o =>
                {
                    badRemark += string.Format("{0}-{1}-{2};", o.BadName, Enum.GetName(typeof(DefectLevelEnum), o.DefectLevel ?? 0), o.BadQty);
                });

                headerRow.CreateCell(1, CellType.String).SetCellValue(i.ToString());
                headerRow.CreateCell(2, CellType.String).SetCellValue(item.ItemName);
                headerRow.CreateCell(7, CellType.String).SetCellValue(item.ItemAc);
                headerRow.CreateCell(9, CellType.String).SetCellValue(item.ItemRe);
                headerRow.CreateCell(11, CellType.String).SetCellValue(item.SampleQty.ToString("0"));
                headerRow.CreateCell(15, CellType.String).SetCellValue(item.QCOrderItemBadModels.Sum(p => p.BadQty).ToString("0"));
                headerRow.CreateCell(18, CellType.String).SetCellValue(badRemark);
                headerRow.CreateCell(30, CellType.String).SetCellValue(Enum.GetName(typeof(DefectLevelEnum), item.DefectLevel));
                headerRow.CreateCell(34, CellType.String).SetCellValue(Enum.GetName(typeof(QcCheckResultEnum), item.Result));
                headerRow.CreateCell(37, CellType.String).SetCellValue(item.Remark);
                IQCQualitativeMergedRegion(workbook, sheet, rowNum);
                var height1 = string.IsNullOrEmpty(item.Remark) ? Convert.ToInt16(0) : Convert.ToInt16(((FileUtils.GetLength(item.Remark) / 20) + 1) * 280);
                var height2 = Convert.ToInt16(((FileUtils.GetLength(item.ItemName) / 24) + 1) * 280);
                headerRow.Height = height1 > height2 ? height1 : height2;
                ExcelStyleUtils.SetRowWrapText(workbook, headerRow);
            }

            MergedRegion(sheet, rowNum - rowCount, rowNum, 0, 0, workbook);
        }

        private void IQCQualitativeMergedRegion(IWorkbook workbook, ISheet sheet, int rowNum)
        {
            MergedRegion(sheet, rowNum, rowNum, 2, 6, workbook);
            MergedRegion(sheet, rowNum, rowNum, 7, 8, workbook);
            MergedRegion(sheet, rowNum, rowNum, 9, 10, workbook);
            MergedRegion(sheet, rowNum, rowNum, 11, 14, workbook);
            MergedRegion(sheet, rowNum, rowNum, 15, 17, workbook);
            MergedRegion(sheet, rowNum, rowNum, 18, 29, workbook);
            MergedRegion(sheet, rowNum, rowNum, 30, 33, workbook);
            MergedRegion(sheet, rowNum, rowNum, 34, 36, workbook);
            MergedRegion(sheet, rowNum, rowNum, 37, 38, workbook);
        }

        private int AddBatchItemDataIQCHeaderRow(IWorkbook workbook, ISheet sheet, ref int rowNum, List<QCOrderItemModel> items, List<QCOrderCategoryDefectModel> defects, string categoryName)
        {
            ICellStyle boldStyle = ExcelStyleUtils.GetExcelOQCStyle(workbook);
            var rowCount = 0;
            var defectStr = string.Empty;
            var hasDefect = false;
            var inspectionLevel = string.Empty;
            if (defects != null && defects.Any(p => p.DefectLevel != 0))
            {
                var defect = defects.OrderByDescending(p => p.SampleQty).FirstOrDefault();
                defects.ForEach(o =>
                {
                    if (o.DefectLevel != 0)
                    {
                        defectStr += string.Format("{0}类:(AQL:{1} Ac={2} Re={3}) ; ", Enum.GetName(typeof(DefectLevelEnum), o.DefectLevel), o.AQL, o.ItemAc, o.ItemRe);
                    }
                });

                ///inspectionLevel = string.Format("检验水平:{0} 抽样数:{1}PS", defect.StdName + defect.InspectionLevelCode, defect.SampleQty.HasValue ? defect.SampleQty.Value.ToString("0") : "0");
                hasDefect = true;
            }

            if (hasDefect)
            {
                ///大类缺陷等级
                rowCount++;
                IRow headerRow = sheet.CreateRow(rowNum);
                headerRow.CreateCell(0, CellType.String).SetCellValue(categoryName);
                headerRow.CreateCell(1, CellType.String).SetCellValue(defectStr);
                MergedRegion(sheet, rowNum, rowNum, 1, 38, workbook);
                headerRow.Cells.ForEach(cell =>
                {
                    cell.CellStyle = boldStyle;
                });

                rowNum++;
            }

            return rowCount;
        }

        /// <summary>
        /// 定量模板
        /// </summary>
        /// <param name="batchItem"></param>
        /// <param name="workbook"></param>
        /// <param name="sheet"></param>
        /// <param name="rowNum"></param>
        private void AddBatchItemDataIQCRationRow(IWorkbook workbook, ISheet sheet, ref int rowNum, List<QCOrderItemModel> items, List<QCOrderCategoryDefectModel> defects, string categoryName)
        {
            ///var rowCount = AddBatchItemDataOQCHeaderRow(workbook, sheet, ref rowNum, items, defects, categoryName);
            IRow headerRow = sheet.CreateRow(rowNum);
            headerRow.CreateCell(0, CellType.String).SetCellValue(categoryName);
            headerRow.CreateCell(1, CellType.String).SetCellValue("NO");
            headerRow.CreateCell(2, CellType.String).SetCellValue("检验项目");
            headerRow.CreateCell(7, CellType.String).SetCellValue("范围值");
            headerRow.CreateCell(12, CellType.String).SetCellValue("抽样数");
            headerRow.CreateCell(14, CellType.String).SetCellValue("测试结果(每个尺寸项目随机记录实测值)");
            headerRow.CreateCell(34, CellType.String).SetCellValue("判定");
            headerRow.CreateCell(37, CellType.String).SetCellValue("备注");
            MergedRegion(sheet, rowNum, rowNum, 2, 6, workbook);
            MergedRegion(sheet, rowNum, rowNum, 7, 11, workbook);
            MergedRegion(sheet, rowNum, rowNum, 12, 13, workbook);
            MergedRegion(sheet, rowNum, rowNum, 14, 33, workbook);
            MergedRegion(sheet, rowNum, rowNum, 34, 36, workbook);
            MergedRegion(sheet, rowNum, rowNum, 37, 38, workbook);
            ExcelStyleUtils.SetRowWrapText(workbook, headerRow);
            var rowCount = 0;

            for (var i = 1; i <= items.Count; i++)
            {
                rowNum++;
                rowCount++;
                var item = items[i - 1];
                headerRow = sheet.CreateRow(rowNum);
                var rowCounts = item.QCOrderItemSamplingModels == null || !item.QCOrderItemSamplingModels.Any() ? 2 : ((item.QCOrderItemSamplingModels.Count / 10) + ((item.QCOrderItemSamplingModels.Count % 10) > 0 ? 1 : 0)) * 2;
                var standvalue = (item.LowerValue.HasValue ? item.LowerValue.ToString() : "-") + "/" + (item.StandardValue.HasValue ? item.StandardValue.ToString() : "-") + "/" + (item.UpperValue.HasValue ? item.UpperValue.ToString() : "-");
                var standvalueRows = Convert.ToInt16((standvalue.Length / 7) + 1);
                short remarkRows = string.IsNullOrEmpty(item.Remark) ? Convert.ToInt16(0) : Convert.ToInt16((FileUtils.GetLength(item.Remark) / 20) + 1);
                var itemNameRows = Convert.ToInt16((FileUtils.GetLength(item.ItemName) / 24) + 1);
                itemNameRows = itemNameRows > standvalueRows ? itemNameRows : standvalueRows;
                itemNameRows = itemNameRows > remarkRows ? itemNameRows : remarkRows;
                headerRow.Height = 280;
                if (itemNameRows > rowCounts)
                {
                    headerRow.Height = Convert.ToInt16((itemNameRows - rowCounts + 1) * 280);
                }

                headerRow.CreateCell(1, CellType.String).SetCellValue(i.ToString());
                headerRow.CreateCell(2, CellType.String).SetCellValue(item.ItemName);
                headerRow.CreateCell(7, CellType.String).SetCellValue(standvalue);
                headerRow.CreateCell(12, CellType.String).SetCellValue(item.SampleQty.ToString("0"));
                int index = 14;
                ///样本值序号
                int t = 0;
                for (t = 0; t <= item.QCOrderItemSamplingModels.Count / 10; t++)
                {
                    if (t > 0)
                    {
                        rowNum++;
                        rowCount++;
                        headerRow = sheet.CreateRow(rowNum);
                    }

                    for (var j = 1; j <= 10; j++)
                    {
                        headerRow.CreateCell(index + ((j - 1) * 2), CellType.String).SetCellValue((j + (t * 10)).ToString());
                    }

                    for (var j = 1; j <= 10; j++)
                    {
                        MergedRegion(sheet, rowNum, rowNum, index + ((j - 1) * 2), index + (j * 2) - 1, workbook);
                    }

                    if (t == 0)
                    {
                        headerRow.CreateCell(34, CellType.String).SetCellValue(Enum.GetName(typeof(QcCheckResultEnum), item.Result));
                        headerRow.CreateCell(37, CellType.String).SetCellValue(item.Remark);
                    }

                    ExcelStyleUtils.SetRowWrapText(workbook, headerRow);

                    ///样本值
                    rowNum++;
                    rowCount++;
                    headerRow = sheet.CreateRow(rowNum);
                    headerRow.Height = 280;

                    for (var j = 1; j <= 10; j++)
                    {
                        if (item.QCOrderItemSamplingModels == null || !item.QCOrderItemSamplingModels.Any())
                        {
                            if (j == 1)
                            {
                                headerRow.CreateCell(index + ((j - 1) * 2), CellType.String).SetCellValue("实配");
                            }
                            else
                            {
                                headerRow.CreateCell(index + ((j - 1) * 2), CellType.String).SetCellValue(string.Empty);
                            }
                        }
                        else
                        {
                            item.QCOrderItemSamplingModels = item.QCOrderItemSamplingModels.OrderBy(p => p.Index).ToList();
                            var sampIndex = j + (t * 10);
                            if (item.QCOrderItemSamplingModels != null && item.QCOrderItemSamplingModels.Count >= sampIndex)
                            {
                                headerRow.CreateCell(index + ((j - 1) * 2), CellType.String).SetCellValue(item.QCOrderItemSamplingModels[sampIndex - 1].Sample.ToString());
                            }
                            else
                            {
                                headerRow.CreateCell(index + ((j - 1) * 2), CellType.String).SetCellValue(string.Empty);
                            }
                        }
                    }

                    for (var j = 1; j <= 10; j++)
                    {
                        MergedRegion(sheet, rowNum, rowNum, index + ((j - 1) * 2), index + (j * 2) - 1, workbook);
                    }
                }

                MergedRegion(sheet, rowNum - ((2 * t) - 1), rowNum, 1, 1, workbook);
                MergedRegion(sheet, rowNum - ((2 * t) - 1), rowNum, 2, 6, workbook);
                MergedRegion(sheet, rowNum - ((2 * t) - 1), rowNum, 7, 11, workbook);
                MergedRegion(sheet, rowNum - ((2 * t) - 1), rowNum, 12, 13, workbook);
                MergedRegion(sheet, rowNum - ((2 * t) - 1), rowNum, 34, 36, workbook);
                MergedRegion(sheet, rowNum - ((2 * t) - 1), rowNum, 37, 38, workbook);
                ///rowCount += (2 * t) - 1;
                ExcelStyleUtils.SetRowWrapText(workbook, headerRow);
            }

            MergedRegion(sheet, rowNum - rowCount, rowNum, 0, 0, workbook);
        }

        /// <summary>
        /// OQC表尾
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="sheet"></param>
        /// <param name="rowNum"></param>
        /// <param name="orderModel"></param>
        private void GenerateSheetIQCFooter(IWorkbook workbook, ISheet sheet, int rowNum, IQCOrderModel orderModel)
        {
            ICellStyle boldStyle = ExcelStyleUtils.GetExcelOQCStyle(workbook);
            var versionModel = orderModel.VersionModels.FirstOrDefault();
            var detail = orderModel.DetailModels.FirstOrDefault();
            IRow headerRow = sheet.CreateRow(rowNum);
            if (orderModel.KABU == 0)
            {
                headerRow.Height = 3 * 256;
                headerRow.CreateCell(0, CellType.String).SetCellValue("RoHS确认");
                headerRow.CreateCell(4, CellType.String).SetCellValue(string.Empty);
                MergedRegion(sheet, rowNum, rowNum, 0, 3, workbook);
                MergedRegion(sheet, rowNum, rowNum, 4, 38, workbook);
                headerRow.Cells.ForEach(cell =>
                {
                    cell.CellStyle = boldStyle;
                });

                rowNum++;
                headerRow = sheet.CreateRow(rowNum);
            }

            headerRow.Height = 3 * 256;
            headerRow.CreateCell(0, CellType.String).SetCellValue("备注");
            headerRow.CreateCell(4, CellType.String).SetCellValue(versionModel.Remark);
            MergedRegion(sheet, rowNum, rowNum, 0, 3, workbook);
            MergedRegion(sheet, rowNum, rowNum, 4, 38, workbook);
            headerRow.Cells.ForEach(cell =>
            {
                cell.CellStyle = boldStyle;
            });

            rowNum++;
            if (orderModel.KABU == 1)
            {
                headerRow = sheet.CreateRow(rowNum);
                headerRow.CreateCell(0, CellType.String).SetCellValue("审核");
                headerRow.CreateCell(3, CellType.String).SetCellValue(orderModel.AuditUser);
                headerRow.CreateCell(6, CellType.String).SetCellValue("检验员");
                headerRow.CreateCell(12, CellType.String).SetCellValue(orderModel.CheckUserName);
                headerRow.CreateCell(16, CellType.String).SetCellValue("表单编号");
                headerRow.CreateCell(20, CellType.String).SetCellValue(versionModel.TableCode);
                headerRow.CreateCell(26, CellType.String).SetCellValue("保存年限");
                headerRow.CreateCell(30, CellType.String).SetCellValue(versionModel.ShelfLife + "年");
                headerRow.CreateCell(32, CellType.String).SetCellValue("版本号");
                headerRow.CreateCell(35, CellType.String).SetCellValue("1.2");
                MergedRegion(sheet, rowNum, rowNum, 0, 2, workbook);
                MergedRegion(sheet, rowNum, rowNum, 3, 5, workbook);
                MergedRegion(sheet, rowNum, rowNum, 6, 11, workbook);
                MergedRegion(sheet, rowNum, rowNum, 12, 15, workbook);
                MergedRegion(sheet, rowNum, rowNum, 16, 19, workbook);
                MergedRegion(sheet, rowNum, rowNum, 20, 25, workbook);
                MergedRegion(sheet, rowNum, rowNum, 26, 29, workbook);
                MergedRegion(sheet, rowNum, rowNum, 30, 31, workbook);
                MergedRegion(sheet, rowNum, rowNum, 32, 34, workbook);
                MergedRegion(sheet, rowNum, rowNum, 35, 38, workbook);
            }
            else
            {
                headerRow = sheet.CreateRow(rowNum);
                headerRow.CreateCell(0, CellType.String).SetCellValue("审核");
                headerRow.CreateCell(3, CellType.String).SetCellValue(orderModel.AuditUser);
                headerRow.CreateCell(6, CellType.String).SetCellValue("检验员");
                headerRow.CreateCell(14, CellType.String).SetCellValue(orderModel.CheckUserName);
                headerRow.CreateCell(18, CellType.String).SetCellValue("表单编号");
                headerRow.CreateCell(22, CellType.String).SetCellValue(versionModel.TableCode);
                headerRow.CreateCell(28, CellType.String).SetCellValue("保存年限");
                headerRow.CreateCell(32, CellType.String).SetCellValue(versionModel.ShelfLife + "年");
                MergedRegion(sheet, rowNum, rowNum, 0, 2, workbook);
                MergedRegion(sheet, rowNum, rowNum, 3, 5, workbook);
                MergedRegion(sheet, rowNum, rowNum, 6, 13, workbook);
                MergedRegion(sheet, rowNum, rowNum, 14, 17, workbook);
                MergedRegion(sheet, rowNum, rowNum, 18, 21, workbook);
                MergedRegion(sheet, rowNum, rowNum, 22, 27, workbook);
                MergedRegion(sheet, rowNum, rowNum, 28, 31, workbook);
                MergedRegion(sheet, rowNum, rowNum, 32, 38, workbook);
            }

            headerRow.Cells.ForEach(cell =>
            {
                cell.CellStyle = boldStyle;
            });
        }

        /// <summary>
        /// 表头明细
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="sheet"></param>
        /// <param name="rowNum"></param>
        /// <param name="orderModel"></param>
        private void GenerateSheetIQCHeader(IWorkbook workbook, ISheet sheet, int rowNum, IQCOrderModel orderModel)
        {
            ICellStyle boldStyle = ExcelStyleUtils.GetExcelOQCStyle(workbook);
            var versionModel = orderModel.VersionModels.FirstOrDefault();
            var detail = orderModel.DetailModels.FirstOrDefault();
            IRow headerRow = sheet.CreateRow(rowNum);
            var saleCode = string.Empty;
            if (!string.IsNullOrEmpty(orderModel.SaleCode))
            {
                saleCode = orderModel.SaleCode;
                if (!string.IsNullOrEmpty(orderModel.SaleRow))
                {
                    saleCode += orderModel.SaleRow.Replace("000", "-");
                }
            }

            headerRow.CreateCell(0, CellType.String).SetCellValue("到料日期");
            headerRow.CreateCell(3, CellType.String).SetCellValue(detail.SapCreateTime.HasValue ? detail.SapCreateTime.Value.ToString("yyyy-MM-dd") : string.Empty);
            headerRow.CreateCell(6, CellType.String).SetCellValue("采购凭证");
            headerRow.CreateCell(12, CellType.String).SetCellValue(detail.PurchasingCode);
            headerRow.CreateCell(18, CellType.String).SetCellValue("抽样方法");
            headerRow.CreateCell(23, CellType.String).SetCellValue(versionModel.SamplingLevel == 0 ? "正常" : versionModel.SamplingLevel == 1 ? "放宽" : "加严");
            headerRow.CreateCell(34, CellType.String).SetCellValue("判定结论");
            MergedRegion(sheet, rowNum, rowNum, 0, 2, workbook);
            MergedRegion(sheet, rowNum, rowNum, 3, 5, workbook);
            MergedRegion(sheet, rowNum, rowNum, 6, 11, workbook);
            MergedRegion(sheet, rowNum, rowNum, 12, 17, workbook);
            MergedRegion(sheet, rowNum, rowNum, 18, 22, workbook);
            MergedRegion(sheet, rowNum, rowNum, 23, 33, workbook);
            MergedRegion(sheet, rowNum, rowNum, 34, 38, workbook);
            headerRow.Cells.ForEach(cell =>
            {
                cell.CellStyle = boldStyle;
            });

            rowNum++;
            headerRow = sheet.CreateRow(rowNum);
            headerRow.Height = 2 * 256;
            headerRow.CreateCell(0, CellType.String).SetCellValue("检验日期");
            headerRow.CreateCell(3, CellType.String).SetCellValue(versionModel.CreateTime.HasValue ? versionModel.CreateTime.Value.ToString("yyyy-MM-dd") : string.Empty);
            headerRow.CreateCell(6, CellType.String).SetCellValue("采购凭证项目");
            headerRow.CreateCell(12, CellType.String).SetCellValue(detail.PurchasingRow);
            headerRow.CreateCell(18, CellType.String).SetCellValue("物料ID");
            headerRow.CreateCell(23, CellType.String).SetCellValue(orderModel.ProductCode);
            headerRow.CreateCell(27, CellType.String).SetCellValue("供应商简称");
            headerRow.CreateCell(30, CellType.String).SetCellValue(orderModel.ProviderName);
            headerRow.CreateCell(34, CellType.String).SetCellValue(orderModel.KABU == 1 ? orderModel.Result : Enum.GetName(typeof(DecisionPolicyEnum), versionModel.DecisionPolicy));
            MergedRegion(sheet, rowNum, rowNum, 0, 2, workbook);
            MergedRegion(sheet, rowNum, rowNum, 3, 5, workbook);
            MergedRegion(sheet, rowNum, rowNum, 6, 11, workbook);
            MergedRegion(sheet, rowNum, rowNum, 12, 17, workbook);
            MergedRegion(sheet, rowNum, rowNum, 18, 22, workbook);
            MergedRegion(sheet, rowNum, rowNum, 23, 26, workbook);
            MergedRegion(sheet, rowNum, rowNum, 27, 29, workbook);
            MergedRegion(sheet, rowNum, rowNum, 30, 33, workbook);
            if (orderModel.Type == 1)
            {
                MergedRegion(sheet, rowNum, rowNum, 34, 38, workbook);
            }

            ////MergedRegion(sheet, rowNum, rowNum, 35, 39, workbook);
            headerRow.Cells.ForEach(cell =>
            {
                cell.CellStyle = boldStyle;
            });

            var samplingQty = versionModel.SamplingSchemeDefects.Max(p => p.SampleQty).HasValue && versionModel.SamplingSchemeDefects.Max(p => p.SampleQty).Value > 0 ? versionModel.SamplingSchemeDefects.Max(p => p.SampleQty).Value : versionModel.QCOrderItemModels.Max(p => p.SampleQty);
            rowNum++;
            headerRow = sheet.CreateRow(rowNum);
            headerRow.Height = 2 * 256;
            headerRow.CreateCell(0, CellType.String).SetCellValue("规格型号");
            headerRow.CreateCell(3, CellType.String).SetCellValue(orderModel.ProductName);
            headerRow.CreateCell(27, CellType.String).SetCellValue("供应商编号");
            headerRow.CreateCell(30, CellType.String).SetCellValue(orderModel.ProviderCode);
            ///headerRow.CreateCell(35, CellType.String).SetCellValue("A023");
            if (orderModel.Type == 1)
            {
                headerRow.CreateCell(34, CellType.String).SetCellValue(string.Empty);
                MergedRegion(sheet, rowNum, rowNum, 34, 38, workbook);
            }

            MergedRegion(sheet, rowNum, rowNum, 0, 2, workbook);
            MergedRegion(sheet, rowNum, rowNum, 3, 26, workbook);
            MergedRegion(sheet, rowNum, rowNum, 27, 29, workbook);
            MergedRegion(sheet, rowNum, rowNum, 30, 33, workbook);
            headerRow.Cells.ForEach(cell =>
            {
                cell.CellStyle = boldStyle;
            });

            rowNum++;

            if (orderModel.KABU == 1)
            {
                var version = orderModel.VersionModels.OrderByDescending(p => p.Versions).FirstOrDefault();
                var item = version.SamplingSchemeDefects?.OrderByDescending(p => p.SampleQty).FirstOrDefault();
                headerRow = sheet.CreateRow(rowNum);
                headerRow.CreateCell(0, CellType.String).SetCellValue("销售单号");
                headerRow.CreateCell(3, CellType.String).SetCellValue(orderModel.SaleCode);
                headerRow.CreateCell(6, CellType.String).SetCellValue("销售行号");
                headerRow.CreateCell(12, CellType.String).SetCellValue(string.IsNullOrEmpty(orderModel.SaleCode) ? string.Empty : orderModel.SaleRow);
                headerRow.CreateCell(18, CellType.String).SetCellValue("抽样类型/级别");
                headerRow.CreateCell(23, CellType.String).SetCellValue(item == null ? string.Empty : item.InspectionLevelCode);
                MergedRegion(sheet, rowNum, rowNum, 0, 2, workbook);
                MergedRegion(sheet, rowNum, rowNum, 3, 5, workbook);
                MergedRegion(sheet, rowNum, rowNum, 6, 11, workbook);
                MergedRegion(sheet, rowNum, rowNum, 12, 17, workbook);
                MergedRegion(sheet, rowNum, rowNum, 18, 22, workbook);
                MergedRegion(sheet, rowNum, rowNum, 23, 33, workbook);
                headerRow.Cells.ForEach(cell =>
                {
                    cell.CellStyle = boldStyle;
                });

                rowNum++;
                headerRow = sheet.CreateRow(rowNum);
                headerRow.Height = 2 * 256;
                headerRow.CreateCell(0, CellType.String).SetCellValue("到料数量");
                headerRow.CreateCell(3, CellType.String).SetCellValue(orderModel.DetailModels.Sum(p => p.ArrivalQty).ToString("0"));
                headerRow.CreateCell(6, CellType.String).SetCellValue("应检数量");
                headerRow.CreateCell(12, CellType.String).SetCellValue(samplingQty.ToString("0"));
                headerRow.CreateCell(18, CellType.String).SetCellValue("不良数");
                headerRow.CreateCell(23, CellType.String).SetCellValue(versionModel.QCOrderItemModels.Sum(p => p.QCOrderItemBadModels.Sum(t => t.BadQty)).ToString("0"));
                headerRow.CreateCell(27, CellType.String).SetCellValue("不合格处理");
                headerRow.CreateCell(30, CellType.String).SetCellValue(versionModel.DecisionPolicy == 3001 ? string.Empty : Enum.GetName(typeof(DecisionPolicyEnum), versionModel.DecisionPolicy));
                MergedRegion(sheet, rowNum, rowNum, 0, 2, workbook);
                MergedRegion(sheet, rowNum, rowNum, 3, 5, workbook);
                MergedRegion(sheet, rowNum, rowNum, 6, 11, workbook);
                MergedRegion(sheet, rowNum, rowNum, 12, 17, workbook);
                MergedRegion(sheet, rowNum, rowNum, 18, 22, workbook);
                MergedRegion(sheet, rowNum, rowNum, 23, 26, workbook);
                MergedRegion(sheet, rowNum, rowNum, 27, 29, workbook);
                MergedRegion(sheet, rowNum, rowNum, 30, 33, workbook);
                if (orderModel.Type == 1)
                {
                    headerRow.CreateCell(34, CellType.String).SetCellValue(string.Empty);
                    MergedRegion(sheet, rowNum, rowNum, 34, 38, workbook);
                }

                headerRow.Cells.ForEach(cell =>
                {
                    cell.CellStyle = boldStyle;
                });

                rowNum++;
                headerRow = sheet.CreateRow(rowNum);
                headerRow.CreateCell(0, CellType.String).SetCellValue("SAP检验批号");
                headerRow.CreateCell(3, CellType.String).SetCellValue(string.Join(",", orderModel.DetailModels.Select(p => p.Code)));
                headerRow.CreateCell(27, CellType.String).SetCellValue("异常单号");
                headerRow.CreateCell(31, CellType.String).SetCellValue(orderModel.LcpExceptionCode);
                ///headerRow.CreateCell(31, CellType.String).SetCellValue("1000258233");
                ///
                MergedRegion(sheet, rowNum, rowNum, 0, 2, workbook);
                MergedRegion(sheet, rowNum, rowNum, 3, 26, workbook);
                MergedRegion(sheet, rowNum, rowNum, 27, 29, workbook);
                MergedRegion(sheet, rowNum, rowNum, 30, 33, workbook);
                if (orderModel.Type == 1)
                {
                    headerRow.CreateCell(34, CellType.String).SetCellValue(string.Empty);
                    MergedRegion(sheet, rowNum, rowNum, 34, 38, workbook);
                }
                else
                {
                    MergedRegion(sheet, 2, rowNum, 34, 38, workbook);
                }

                headerRow.Cells.ForEach(cell =>
                {
                    cell.CellStyle = boldStyle;
                });

                headerRow.Cells.ForEach(cell =>
                {
                    cell.CellStyle = boldStyle;
                });
            }
            else
            {
                headerRow = sheet.CreateRow(rowNum);
                headerRow.Height = 2 * 256;
                headerRow.CreateCell(0, CellType.String).SetCellValue("SAP检验批号");
                headerRow.CreateCell(3, CellType.String).SetCellValue(string.Join(",", orderModel.DetailModels.Select(p => p.Code)));
                headerRow.CreateCell(6, CellType.String).SetCellValue("到料数量");
                headerRow.CreateCell(12, CellType.String).SetCellValue(orderModel.DetailModels.Sum(p => p.ArrivalQty).ToString("0"));
                headerRow.CreateCell(18, CellType.String).SetCellValue("应检数量");
                headerRow.CreateCell(23, CellType.String).SetCellValue(samplingQty.ToString("0"));
                headerRow.CreateCell(27, CellType.String).SetCellValue("不良数");
                headerRow.CreateCell(30, CellType.String).SetCellValue(versionModel.QCOrderItemModels.Sum(p => p.QCOrderItemBadModels.Sum(t => t.BadQty)).ToString("0"));
                MergedRegion(sheet, rowNum, rowNum, 0, 2, workbook);
                MergedRegion(sheet, rowNum, rowNum, 3, 5, workbook);
                MergedRegion(sheet, rowNum, rowNum, 6, 11, workbook);
                MergedRegion(sheet, rowNum, rowNum, 12, 17, workbook);
                MergedRegion(sheet, rowNum, rowNum, 18, 22, workbook);
                MergedRegion(sheet, rowNum, rowNum, 23, 26, workbook);
                MergedRegion(sheet, rowNum, rowNum, 27, 29, workbook);
                MergedRegion(sheet, rowNum, rowNum, 30, 33, workbook);
                if (orderModel.Type == 1)
                {
                    headerRow.CreateCell(34, CellType.String).SetCellValue(string.Empty);
                    MergedRegion(sheet, rowNum, rowNum, 34, 38, workbook);
                }

                headerRow.Cells.ForEach(cell =>
                {
                    cell.CellStyle = boldStyle;
                });

                rowNum++;
                headerRow = sheet.CreateRow(rowNum);
                headerRow.CreateCell(0, CellType.String).SetCellValue("结果备注");
                headerRow.CreateCell(3, CellType.String).SetCellValue(string.Empty);
                headerRow.CreateCell(27, CellType.String).SetCellValue("异常单号");
                headerRow.CreateCell(31, CellType.String).SetCellValue(orderModel.LcpExceptionCode);
                ///headerRow.CreateCell(31, CellType.String).SetCellValue("1000258233");
                ///
                MergedRegion(sheet, rowNum, rowNum, 0, 2, workbook);
                MergedRegion(sheet, rowNum, rowNum, 3, 26, workbook);
                MergedRegion(sheet, rowNum, rowNum, 27, 29, workbook);
                MergedRegion(sheet, rowNum, rowNum, 30, 33, workbook);
                if (orderModel.Type == 1)
                {
                    headerRow.CreateCell(34, CellType.String).SetCellValue(string.Empty);
                    MergedRegion(sheet, rowNum, rowNum, 34, 38, workbook);
                }
                else
                {
                    MergedRegion(sheet, 2, rowNum, 34, 38, workbook);
                }

                headerRow.Cells.ForEach(cell =>
                {
                    cell.CellStyle = boldStyle;
                });

                headerRow.Cells.ForEach(cell =>
                {
                    cell.CellStyle = boldStyle;
                });
            }
        }

        /// <summary>
        /// 标题
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="sheet"></param>
        /// <param name="rowNum"></param>
        /// <param name="reportType"></param>
        /// <param name="samplingSchemeName"></param>
        private void GenerateExcelIQCHeader(IWorkbook workbook, ISheet sheet, int rowNum, string sampschemeName)
        {
            ICellStyle boldStyle = ExcelStyleUtils.GetExcelHeaderOQCStyle(workbook);
            IRow headerRow1 = sheet.CreateRow(rowNum);
            sampschemeName = sampschemeName.Split("进料")[0];
            headerRow1.CreateCell(0, CellType.String).SetCellValue($"{sampschemeName}进料检验报告");
            MergedRegion(sheet, rowNum, rowNum, 0, 38, workbook);
            headerRow1.Height = 50 * 10;
            headerRow1.Cells.ForEach(cell =>
            {
                cell.CellStyle = boldStyle;
            });
        }

        private void MergedRegion(ISheet sheet, int firstRow, int lastRow, int firstCol, int lastCol, IWorkbook workbook)
        {
            var region = new NPOI.SS.Util.CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
            sheet.AddMergedRegion(region);
            RegionUtil.SetBorderLeft(1, region, sheet);
            RegionUtil.SetBorderRight(1, region, sheet);
            RegionUtil.SetBorderTop(1, region, sheet);
            RegionUtil.SetBorderBottom(1, region, sheet);
        }

        /// <summary>
        /// OQC设置列宽
        /// </summary>
        /// <param name="sheet"></param>
        private void GenerateSheetSetColunmsWidth(ISheet sheet)
        {
            sheet.SetColumnWidth(0, 2 * 350);
            sheet.SetColumnWidth(1, 3 * 300);
            sheet.SetColumnWidth(2, 5 * 350);
            sheet.SetColumnWidth(3, 4 * 300);
            sheet.SetColumnWidth(4, 5 * 300);
            sheet.SetColumnWidth(5, 3 * 300);
            sheet.SetColumnWidth(6, 5 * 300);
            sheet.SetColumnWidth(7, 2 * 350);
            sheet.SetColumnWidth(8, 1 * 256);
            sheet.SetColumnWidth(9, 2 * 350);
            sheet.SetColumnWidth(10, 1 * 256);

            sheet.SetColumnWidth(11, 1 * 256);
            sheet.SetColumnWidth(12, 2 * 350);
            sheet.SetColumnWidth(13, 1100);

            for (int i = 14; i < 38; i++)
            {
                sheet.SetColumnWidth(i, 3 * 300);
            }

            sheet.SetColumnWidth(38, 16 * 300);
        }
    }
}
